﻿'========================================================================
'功能:網頁名稱及相關設定資料庫存取物件
'作者:andy
'日期: 2011/1/28
'========================================================================

Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Text
Imports System.Data


Namespace OAMS.DAO
    Public Class MenuDAO
        Inherits BaseDAO

        Dim _dbName As String
        Public Sub New(ByVal dbName As String)
            _dbName = dbName
        End Sub

        ''' <summary>
        ''' 新增一筆Menu資料
        ''' </summary>
        ''' <param name="RootID"></param>
        ''' <param name="Name"></param>
        ''' <param name="NeedControl"></param>
        ''' <param name="URL"></param>
        ''' <param name="GroupID"></param>
        ''' <param name="UID"></param>
        ''' <returns>新增一筆Menu資料</returns>
        ''' <remarks>新增一筆Menu資料</remarks>
        Public Function INSERT_NewOne(ByVal RootID As String, ByVal Name As String, ByVal NeedControl As Boolean, ByVal URL As String, ByVal GroupID As String, ByVal UID As String, ByVal IsMenu As Boolean, ByVal Admin As Boolean) As Integer
            Dim sqlParams As SqlParameter() = New SqlParameter(7) {}

            sqlParams(0) = New SqlParameter("@RootID", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = RootID
            sqlParams(1) = New SqlParameter("@Name", SqlDbType.NVarChar, 100)
            sqlParams(1).Value = Name
            sqlParams(2) = New SqlParameter("@NeedControl", SqlDbType.Bit)
            sqlParams(2).Value = NeedControl
            sqlParams(3) = New SqlParameter("@URL", SqlDbType.NVarChar, 100)
            sqlParams(3).Value = URL
            sqlParams(4) = New SqlParameter("@GroupID", SqlDbType.NVarChar, 100)
            sqlParams(4).Value = GroupID
            sqlParams(5) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(5).Value = UID
            sqlParams(6) = New SqlParameter("@IsMenu", SqlDbType.Bit)
            sqlParams(6).Value = IsMenu
            sqlParams(7) = New SqlParameter("@Admin", SqlDbType.Bit)
            sqlParams(7).Value = Admin


            Dim sb As New StringBuilder()


            sb.AppendLine(" declare @ProgramID int select @ProgramID=max(ProgramID)+1  from Menu  where Admin=@Admin  INSERT INTO [Menu] ( [ProgramID],[RootID], [Name], [NeedControl], [URL], [GroupID], [CrtUID],IsMenu,Admin) VALUES (")
            sb.AppendLine("@ProgramID, @RootID, @Name, @NeedControl, @URL, @GroupID, @UID,@IsMenu,@Admin) select ProgramID=@ProgramID")

            sb.AppendLine(" insert into MenuRights (ProgramID,SeqNo,DisplayName,CrtUID,CrtDate,ModiUID,ModiDate)")
            sb.AppendLine("  values(@ProgramID,1,'使用',@UID,getdate(),'',null)")

            Return Integer.Parse(SqlHelper.ExecuteScalar(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).ToString)
        End Function

        ''' <summary>
        ''' 取得有授權的Menu資料
        ''' </summary>
        ''' <param name="UID"></param>
        ''' <returns>取得有授權的Menu資料</returns>
        ''' <remarks>取得有授權的Menu資料</remarks>
        Public Function GetMenuUID(ByVal UID As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}

            sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = UID
            Dim sb As New StringBuilder()
            sb.AppendLine("Select * from Menu where GroupID in (select GroupID from GroupMember where UID=@UID) order by OrderByID ")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)


        End Function



        ''' <summary>
        ''' 取得有授權的Menu資料
        ''' </summary>
        ''' <param name="UID"></param>
        ''' <returns>取得有授權的Menu資料</returns>
        ''' <remarks>取得有授權的Menu資料</remarks>
        Public Function GetMenuByRootNameUID(ByVal RootID As String, ByVal UID As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}

            sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = UID
            sqlParams(1) = New SqlParameter("@RootID", SqlDbType.NVarChar, 100)
            sqlParams(1).Value = RootID
            Dim sb As New StringBuilder()
            sb.AppendLine("Select * from Menu where RootID=@RootID  and  GroupID in (select GroupID from GroupMember where UID=@UID) order by OrderByID ")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)


        End Function


        ''' <summary>
        ''' 取得有授權的Menu資料
        ''' </summary>
        ''' <param name="UID"></param>
        ''' <returns>取得有授權的Menu資料</returns>
        ''' <remarks>取得有授權的Menu資料</remarks>
        Public Function GetMenuByRootNameUIDAllColumn(ByVal RootID As String, ByVal UID As String, ByVal GroupID As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(2) {}

            sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = UID
            sqlParams(1) = New SqlParameter("@RootID", SqlDbType.NVarChar, 100)
            sqlParams(1).Value = RootID
            sqlParams(2) = New SqlParameter("@GroupID", SqlDbType.NVarChar, 100)
            sqlParams(2).Value = GroupID
            Dim sb As New StringBuilder()
            sb.AppendLine("Select C.Name ,A.ProgramID ,A.SeqNo,A.DisplayName  ,isnull(B.UseRight ,0) as UseRight")
            sb.AppendLine("from MenuRights  A")
            '  sb.AppendLine("left join GroupRights  B on A.ProgramID =B.ProgramID  and A.SeqNo =B.SeqNo and B.GroupID =@GroupID")
            sb.AppendLine("left join GroupRights  B on A.ProgramID =B.ProgramID  and A.SeqNo =B.SeqNo and B.GroupID  in ((Select isnull(g2.GroupID,g1.GroupID)  from GroupID g1 left join GroupID g2 on g1.RootID =g2.GroupID where 1=1 and g1.GroupID=@GroupID and g2.RootID is null),@GroupID) ")
            sb.AppendLine("left join Menu C on A.ProgramID =C.ProgramID ")
            sb.AppendLine("where C.RootID=@RootID  and  C.GroupID in ((select GroupID from GroupMember where UID=@UID),(select isnull(g2.GroupID,g.GroupID)  from GroupMember m left join GroupID g on m.GroupID=g.GroupID  left join GroupID g2 on g.RootID=g2.GroupID where m.UID=@UID and g2.RootID is null))")
            sb.AppendLine("order by C.OrderByID   ,C.ProgramID,A.SeqNo   ")
            Dim dt As DataTable = SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
            Dim dt2 As DataTable = New DataTable()
            dt2.Columns.Add("Name", Type.GetType("System.String"))
            dt2.Columns.Add("ProgramID", Type.GetType("System.Int32"))

            Dim jj As Integer = GetMenuByRootNameUIDMaxCol(RootID, UID)
            For ii As Integer = 1 To 20

                dt2.Columns.Add("DisplayName" + ii.ToString("00"), Type.GetType("System.String"))
                dt2.Columns.Add("UseRight" + ii.ToString("00"), Type.GetType("System.Boolean"))
                dt2.Columns.Add("Vis" + ii.ToString("00"), Type.GetType("System.Boolean"))
            Next
            Dim dr2 As DataRow
            Dim drs() As DataRow
            Dim seqNo As Integer
            For Each dr As DataRow In dt.Rows
                drs = dt2.Select("ProgramID=" + dr("ProgramID").ToString)
                seqNo = dr("SeqNo")
                If drs.Length = 0 Then
                    dr2 = dt2.NewRow()
                    dr2("UseRight01") = False
                    dr2("UseRight02") = False
                    dr2("UseRight03") = False
                    dr2("UseRight04") = False
                    dr2("UseRight05") = False
                    dr2("UseRight06") = False
                    dr2("UseRight07") = False
                    dr2("UseRight08") = False
                    dr2("UseRight09") = False
                    dr2("UseRight10") = False
                    dr2("UseRight11") = False
                    dr2("UseRight12") = False
                    dr2("UseRight13") = False
                    dr2("UseRight14") = False
                    dr2("UseRight15") = False
                    dr2("UseRight16") = False
                    dr2("UseRight17") = False
                    dr2("UseRight18") = False
                    dr2("UseRight19") = False
                    dr2("UseRight20") = False
                    dr2("Vis01") = False
                    dr2("Vis02") = False
                    dr2("Vis03") = False
                    dr2("Vis04") = False
                    dr2("Vis05") = False
                    dr2("Vis06") = False
                    dr2("Vis07") = False
                    dr2("Vis08") = False
                    dr2("Vis09") = False
                    dr2("Vis10") = False
                    dr2("Vis11") = False
                    dr2("Vis12") = False
                    dr2("Vis13") = False
                    dr2("Vis14") = False
                    dr2("Vis15") = False
                    dr2("Vis16") = False
                    dr2("Vis17") = False
                    dr2("Vis18") = False
                    dr2("Vis19") = False
                    dr2("Vis20") = False



                    dr2("Name") = dr("Name").ToString
                    dr2("ProgramID") = dr("ProgramID")
                    dr2("Vis" + seqNo.ToString("00")) = True
                    dr2("DisplayName" + seqNo.ToString("00")) = dr("DisplayName").ToString
                    If dr("UseRight").ToString = "True" Then
                        dr2("UseRight" + seqNo.ToString("00")) = True
                    Else
                        dr2("UseRight" + seqNo.ToString("00")) = False
                    End If
                    dt2.Rows.InsertAt(dr2, dt2.Rows.Count)
                Else
                    drs(0)("DisplayName" + seqNo.ToString("00")) = dr("DisplayName").ToString

                    If dr("UseRight").ToString = "True" Then
                        drs(0)("UseRight" + seqNo.ToString("00")) = True
                    Else
                        drs(0)("UseRight" + seqNo.ToString("00")) = False
                    End If
                    drs(0)("Vis" + seqNo.ToString("00")) = True

                End If


            Next



            Return dt2


        End Function



        ''' <summary>
        ''' 取得有授權的Menu資料
        ''' </summary>
        ''' <param name="UID"></param>
        ''' <returns>取得有授權的Menu資料</returns>
        ''' <remarks>取得有授權的Menu資料</remarks>
        Public Function GetMenuByRootNameUIDMaxCol(ByVal RootID As String, ByVal UID As String) As Integer
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}

            sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = UID
            sqlParams(1) = New SqlParameter("@RootID", SqlDbType.NVarChar, 100)
            sqlParams(1).Value = RootID

            Dim sb As New StringBuilder()
            sb.AppendLine("Select max(A.SeqNo) as SeqNo")
            sb.AppendLine("from MenuRights  A")

            sb.AppendLine("left join Menu C on A.ProgramID =C.ProgramID ")
            sb.AppendLine("where C.RootID=@RootID  and  C.GroupID in ((select isnull(g.RootID,g.GroupID)  from GroupMember m left join GroupID g on m.GroupID=g.GroupID where m.UID=@UID),(select GroupID from GroupMember where UID=@UID)) ")
            Try
                Return Integer.Parse(SqlHelper.ExecuteScalar(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).ToString)
            Catch ex As Exception
                Return 0
            End Try


        End Function

        ''' <summary>
        ''' 取得畫面檔資料
        ''' </summary>
        ''' <returns>取得畫面檔資料</returns>
        ''' <remarks>取得畫面檔資料</remarks>
        Public Function GetMenu() As DataTable
            Dim sb As New StringBuilder()
            sb.AppendLine("Select ProgramID,RootID,Name,NeedControl,URL,GroupID  ,QueryString, ChnName,EngName,IsMenu,OrderByID ,IsRoot,IsNewWin ,Param,MenuType from Menu  order by OrderByID ,ProgramID desc  ")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString()).Tables(0)
        End Function

        ''' <summary>
        ''' 取得畫面檔資料
        ''' </summary>
        ''' <returns>取得畫面檔資料</returns>
        ''' <remarks>取得畫面檔資料</remarks>
        Public Function GetMenuByRootName(ByVal RootID As String) As DataTable
            Dim sb As New StringBuilder()
            sb.AppendLine("Select A.ProgramID, A.RootID ,A.Name,A.NeedControl,A.URL,A.GroupID  ,A.QueryString, A.ChnName,A.EngName,A.IsMenu,A.OrderByID,A.IsRoot,A.IsNewWin ,B.Name as RootName ,A.Param,A.MenuType from Menu A")
            sb.AppendLine("left join Menu B on A.RootID=B.ProgramID  where A.RootID=@RootID  and A.Admin=1 order by A.OrderByID ,A.ProgramID desc  ")
            Dim sqlParams As SqlParameter() = New SqlParameter(0) {}
            sqlParams(0) = New SqlParameter("@RootID", SqlDbType.NVarChar)
            sqlParams(0).Value = RootID
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        End Function
        ''' <summary>
        ''' 取得User自訂畫面檔資料
        ''' </summary>
        ''' <returns> 取得User自訂畫面檔資料</returns>
        ''' <remarks> 取得User自訂畫面檔資料</remarks>
        Public Function GetUserMenuByRootName(ByVal RootID As String) As DataTable
            Dim sb As New StringBuilder()
            sb.AppendLine("Select A.ProgramID, A.RootID ,A.Name,A.NeedControl,A.URL,A.GroupID  ,A.QueryString, A.ChnName,A.EngName,A.IsMenu,A.OrderByID,A.IsRoot,A.IsNewWin ,B.Name as RootName from Menu A")
            sb.AppendLine("left join Menu B on A.RootID=B.ProgramID  where A.RootID=@RootID  and A.Admin=0 order by A.OrderByID ,A.ProgramID desc  ")
            Dim sqlParams As SqlParameter() = New SqlParameter(0) {}
            sqlParams(0) = New SqlParameter("@RootID", SqlDbType.NVarChar)
            sqlParams(0).Value = RootID
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        End Function
        ''' <summary>
        ''' 檢查某個URL 是否已存在,要避免重復KeyIn
        ''' </summary>
        ''' <param name="URL"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function UrlIsExist(ByVal URL As String, ByVal ProgramID As Integer) As Boolean
            Dim sb As New StringBuilder()
            sb.AppendLine("Select ProgramID from Menu  where URL=@URL  and ProgramID <>@ProgramID order by OrderByID ,ProgramID desc  ")
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            sqlParams(0) = New SqlParameter("@URL", SqlDbType.NVarChar)
            sqlParams(0).Value = URL
            sqlParams(1) = New SqlParameter("@ProgramID", SqlDbType.NVarChar)
            sqlParams(1).Value = ProgramID
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0).Rows.Count > 0
        End Function

        ''' <summary>
        ''' 取得選單名稱
        ''' </summary>
        ''' <returns>取得畫面檔資料</returns>
        ''' <remarks>取得畫面檔資料</remarks>
        Public Function GetRootName() As DataTable
            Dim sb As New StringBuilder()
            sb.AppendLine("Select distinct rtrim(Name)as Name ,ProgramID,OrderByID ,  RootID  from Menu  where IsRoot=1  and Name <>'' order by  OrderByID ")
           
            Dim dt As DataTable = SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString()).Tables(0)
            Dim dr As DataRow
            dr = dt.NewRow()
            dr("Name") = ""
            dr("ProgramID") = 0
            dt.Rows.InsertAt(dr, 0)
            Return dt
        End Function

        ''' <summary>
        ''' 取得選單名稱
        ''' </summary>
        ''' <returns>取得畫面檔資料</returns>
        ''' <remarks>取得畫面檔資料</remarks>
        Public Function GetRootNameUID(ByVal UID As String) As DataTable
            Dim sb As New StringBuilder()
            sb.AppendLine("Select distinct   A.ProgramID ,A.Name from Menu  A where  A.IsRoot = 1 and   A.GroupID in ((select GroupID from GroupMember where UID=@UID),(select isnull(g2.GroupID,g.GroupID)   from GroupMember m left join GroupID g on m.GroupID=g.GroupID left join GroupID g2 on g.RootID=g2.GroupID where m.UID=@UID and g2.RootID is null))  order by  A.Name ")
            Dim sqlParams As SqlParameter() = New SqlParameter(0) {}
            sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar)
            sqlParams(0).Value = UID
            Dim dt As DataTable = SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
            Dim dr As DataRow
            dr = dt.NewRow()
            dr("Name") = ""
            dr("ProgramID") = 0
            dt.Rows.InsertAt(dr, 0)
            Return dt
        End Function
        ''' <summary>
        ''' 修改Memu資料
        ''' </summary>
        ''' <param name="ProgramID"></param>
        ''' <param name="RootID"></param>
        ''' <param name="Name"></param>
        ''' <param name="NeedControl"></param>
        ''' <param name="URL"></param>
        ''' <param name="GroupID"></param>
        ''' <param name="UID"></param>
        ''' <remarks>修改Memu資料</remarks>
        Public Sub UpdateMenu(ByVal ProgramID As Integer, ByVal RootID As String, ByVal Name As String, ByVal NeedControl As Boolean, ByVal URL As String, ByVal GroupID As String, ByVal UID As String, ByVal QueryString As Boolean, ByVal ChnName As String, _
                              ByVal EngName As String, ByVal IsMenu As Boolean, ByVal OrderByID As String, ByVal IsRoot As Boolean, ByVal IsNewWin As Boolean, ByVal Param As Integer, ByVal MenuType As Integer)
            Dim sqlParams As SqlParameter() = New SqlParameter(15) {}
            sqlParams(0) = New SqlParameter("@ProgramID", SqlDbType.Int)
            sqlParams(0).Value = ProgramID
            sqlParams(1) = New SqlParameter("@RootID", SqlDbType.NVarChar, 100)
            sqlParams(1).Value = RootID.Trim
            sqlParams(2) = New SqlParameter("@Name", SqlDbType.NVarChar, 100)
            sqlParams(2).Value = Name.Trim
            sqlParams(3) = New SqlParameter("@NeedControl", SqlDbType.Bit)
            sqlParams(3).Value = NeedControl
            sqlParams(4) = New SqlParameter("@URL", SqlDbType.NVarChar, 100)
            sqlParams(4).Value = URL
            sqlParams(5) = New SqlParameter("@GroupID", SqlDbType.NVarChar, 100)
            sqlParams(5).Value = GroupID
            sqlParams(6) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(6).Value = UID
            sqlParams(7) = New SqlParameter("@QueryString", SqlDbType.Bit, QueryString)
            sqlParams(7).Value = QueryString
            sqlParams(8) = New SqlParameter("@ChnName", SqlDbType.NVarChar, 100)
            sqlParams(8).Value = ChnName
            sqlParams(9) = New SqlParameter("@EngName", SqlDbType.NVarChar, 100)
            sqlParams(9).Value = EngName
            sqlParams(10) = New SqlParameter("@IsMenu", SqlDbType.Bit)
            sqlParams(10).Value = IsMenu
            sqlParams(11) = New SqlParameter("@OrderByID", SqlDbType.NVarChar)
            sqlParams(11).Value = OrderByID
            sqlParams(12) = New SqlParameter("@IsRoot", SqlDbType.Bit)
            sqlParams(12).Value = IsRoot
            sqlParams(13) = New SqlParameter("@IsNewWin", SqlDbType.Bit)
            sqlParams(13).Value = IsNewWin
            sqlParams(14) = New SqlParameter("@Param", SqlDbType.Int)
            sqlParams(14).Value = Param
            sqlParams(15) = New SqlParameter("@MenuType", SqlDbType.Int)
            sqlParams(15).Value = MenuType
            Dim sb As New StringBuilder()
            sb.AppendLine("update Menu  set RootID=@RootID, Name=@Name,ChnName=@ChnName,EngName =@EngName,NeedControl=@NeedControl,URL=@URL , GroupID=@GroupID ,ModiUID=@UID ,ModiDate=GetDate(),QueryString=@QueryString,IsMenu=@IsMenu,")
            sb.AppendLine("OrderByID=@OrderByID ,IsRoot=@IsRoot,IsNewWin=@IsNewWin,Param=@Param ,MenuType=@MenuType where ProgramID=@ProgramID")
            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
        End Sub
        Public Function GetMenuType() As DataTable

            Dim sb As New StringBuilder()
            sb.AppendLine("Select * from MenuType  ")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString()).Tables(0)
        End Function

        ''' <summary>
        ''' 取得某一畫面權限
        ''' </summary>
        ''' <param name="ProgramID"></param>
        ''' <returns>取得某一畫面權限</returns>
        ''' <remarks>取得某一畫面權限</remarks>
        Public Function GetMenuRights(ByVal ProgramID As Integer) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            sqlParams(0) = New SqlParameter("@ProgramID", SqlDbType.Int)
            sqlParams(0).Value = ProgramID
            Dim sb As New StringBuilder()
            sb.AppendLine("Select SeqNo, DisplayName ,0  as canModi from MenuRights where ProgramID=@ProgramID  union select 0 as SeqNo,'' as Display ,1 as canModi order by SeqNo  ")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        End Function

        ''' <summary>
        ''' 新增或修改一筆MenuRights資料
        ''' </summary>
        ''' <param name="ProgramID"></param>
        ''' <param name="SeqNo"></param>
        ''' <param name="DisplayName"></param>
        ''' <param name="UID"></param>
        ''' <remarks>新增或修改一筆MenuRights資料</remarks>
        Public Sub UpdateMenuRights(ByVal ProgramID As Integer, ByVal SeqNo As Integer, ByVal DisplayName As String, ByVal UID As String)
            Dim sqlParams As SqlParameter() = New SqlParameter(4) {}
            sqlParams(0) = New SqlParameter("@ProgramID", SqlDbType.Int)
            sqlParams(0).Value = ProgramID
            sqlParams(1) = New SqlParameter("@SeqNo", SqlDbType.Int)
            sqlParams(1).Value = SeqNo
            sqlParams(2) = New SqlParameter("@DisplayName", SqlDbType.NVarChar)
            sqlParams(2).Value = DisplayName
            sqlParams(3) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(3).Value = UID

            Dim sb As New StringBuilder()
            If SeqNo = 0 Then
                sb.AppendLine("declare @SeqNo1 int select @SeqNo1=max(SeqNo)+1 from MenuRights where ProgramID=@ProgramID insert into MenuRights (ProgramID,SeqNo,DisplayName,CrtUID,CrtDate,ModiUID,ModiDate)")
                sb.AppendLine("values(@ProgramID,isnull(@SeqNo1,1),@DisplayName,@UID,getdate(),'',null)")
                sb.AppendLine(" select AA=@SeqNo1")
            Else
                sb.AppendLine("update  MenuRights  set DisplayName=@DisplayName,ModiUID=@UID,ModiDate=GetDate()")
                sb.AppendLine("where ProgramID= @ProgramID and SeqNo=@SeqNo")

            End If

            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
        End Sub
        ''' <summary>
        ''' 
        ''' </summary>
        ''' <param name="RootID"></param>
        ''' <param name="UID"></param>
        ''' <param name="MenuType">選單種類</param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function GetMenuByGroupRights(ByVal RootID As Integer, ByVal UID As String, ByVal MenuType As Integer) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(2) {}
            sqlParams(0) = New SqlParameter("@RootID", SqlDbType.Int)
            sqlParams(0).Value = RootID
            sqlParams(1) = New SqlParameter("@UID", SqlDbType.NVarChar)
            sqlParams(1).Value = UID
            sqlParams(2) = New SqlParameter("@MenuType", SqlDbType.NVarChar)
            sqlParams(2).Value = MenuType
            Dim sb As New StringBuilder()
            sb.AppendLine("select distinct A.IsNewWin,A.ProgramID,A.URL ,A.RootID ,A.Name ,A.ChnName,A.EngName ,  A.OrderByID from Menu A   left join MenuRights B on A.ProgramID=B.ProgramID   left join GroupRights C on C.ProgramID=B.ProgramID and  C.SeqNo=B.SeqNo ")
            sb.AppendLine("  left join GroupMember D on C.GroupID=D.GroupID  where ( (A.NeedControl=0)   or   (  D.GroupID in ((select GroupID from GroupMember where UID=@UID),(select isnull(g2.GroupID,g.GroupID)   from GroupMember m left join GroupID g on m.GroupID=g.GroupID left join GroupID g2 on g.RootID=g2.GroupID where m.UID=@UID and g2.RootID is null))    and C.UseRight=1) ) and A.IsMenu =1 and A.RootID=@RootID  and A.MenuType=@MenuType order by A.OrderByID")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)


        End Function
          

    End Class


End Namespace


